﻿
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_GetListAllBYUserName]
	(
	@username varchar(50)
	)
AS
BEGIN

declare @id int
select @id=id from employee where username=@username


--0:
/***************员工基本信息*******************/
	select 
			id,
			UserName,
			EName,
			ENumber,
			ETempNumber,
			Sex,
			IsNull(CompanyId,0) as CompanyId,
			(select CompanyName from Company where Id = CompanyId) as CompanyName,
			DepartmentId,
			(select DName from Department  where Id = DepartmentId) as DepartmentName,
			IsNull(PostCategory,0) as PostCategory,
			isnull((select Title from Post_Category where Post_Category.Id = PostCategory),'...') as PostCategoryName,
			PostId,
			(select PostName from Post where Id = Employee.PostId) as PostName,
			GradeId,
			(select Title from Grade where Id = GradeId) as GradeName,
			State,
			(select Title from Employee_State where Id = State) as StateName,
			convert(varchar(10),RuzhiDate,120) as RuzhiDate,
			LizhiDate,
			Birthday,
			Rtx,
			OfficePhone,
			HomePhone,
			MobilePhone,
			Email,
			IDcard,
			Address,
			Education,
			(select Title from Employee_Education where Id = Education) as EducationName,
			HeadPic,
			WageNumber,
			isnull(WageCardNo,'') as WageCardNo,
			WorkYM,
			Marriage,
			PoliticsStatus,
			Pluralist,
			Cuenta,
			UpName,
			UpTime,
			(select top 1  auditperson from employee_entry where employee_entry.eid=employee.id  order by id desc) as AuditPerson,
			(select PostName from post where id=postid) as PostName,
			(select title from Grade where id=gradeid) as GradeName,
			 ZzDate,
			ProbationFrom,
			ProbationTo,
			(select (datediff(day,RuzhiDate,getdate())/30)) as WorkAge,
			ZhiWu,
			IsTxlz,
			IsCore,
			Pid,
			(Select p.Title From Province p  where p.Id=Employee.Pid) as Province,
			Cid,
			(Select c.Title From city c  where c.Id=Employee.cid) as City,
			IsInsured,
			isnull(OldEName,'') AS OldEName,
			isnull(Nation,'') AS Nation,
			isnull(ShenGao,0) AS ShenGao,
			isnull(BloodType,'') AS BloodType,
			isnull(YXZY,'') AS YXZY,
			isnull(SkillName,'') AS SkillName,
			isnull(NativePlace,'') AS NativePlace,
			isnull(QQ,'') AS QQ,
			isnull(ForeignLanguage,'') AS ForeignLanguage,
			isnull(DriveType,'') AS DriveType,
			isnull(Hobby,'') AS Hobby,
			ISNULL(XZJB,'') AS XZJB,GID,
						convert(varchar(7),(select top 1 enddate from (select (convert (datetime,enddate+'-01')) enddate from Employee_EducationResume where Employee_EducationResume.eid=Employee.id ) as b order by enddate desc ),120) as bysj

		from
			Employee
		where 
			id = @id
/***************员工基本信息END*******************/			

--1:
/***************员工工资*******************/				
Select 
	(isnull((select Wage from Grade where id=E.GradeId),0)) as BasicWage
	,(isnull((select p.PostWage from Post p where p.id=E.PostId),0)) as PostWage
	,(datediff(mm,ISNULL(e.RuzhiDate,GetDate()),GetDate())/12*100) as WorkWage
	--,(datediff(dd,ISNULL(e.ZzDate,GetDate()),GetDate())/365*100) as WorkWage
	,(isnull((select JxWage from Post where id=E.PostId),0)) as JxWage
	,(isnull((select ManQin from Post where id=E.PostId),0)) as ManQin
	,(isnull((SELECT top(1) isnull(QtAllowance,0) FROM Employee_Wage w where w.UserName=E.UserName  order by w.CreateDate desc),0)) as QtAllowance
	,IsNull((Select Sum(FA.Amount) From Employee_FixedAllowance FA Where FA.UserName = E.UserName ),0) as FAllowance
	,ISNULL((Select Sum(EA.Amount) From Employee_Allowance EA Where PostId=e.PostId and EA.Pid=E.Pid and EA.Cid=E.Cid),0) as Allowance
	From Employee E
	Where E.Id=@Id
/***************员工工资END*******************/	

--2:				
/****************工作经历************************/		
select * from Employee_WorkExperience where eid=@id
/****************工作经历END************************/

--3:
/**********************奖励记录，Temp=0：奖励，=1惩罚*************************/
	SELECT Id,EId,AContent,EntryPeople,EntryTime,EName,Temp,
	ISNULL(Reason,'') AS Reason,
	(SELECT d.DName from Department d WHERE d.Id=Did) AS DName,
	(SELECT c.CompanyName FROM Company c WHERE c.Id=Award_Punish.Cid) AS CName	
	 from Award_Punish 
	where EId=@id AND Temp = 0
	ORDER BY EntryTime desc
/**********************奖惩记录END*************************/
--4:
/**********************惩罚记录，Temp=0：奖励，=1惩罚*************************/
	SELECT Id,EId,AContent,EntryPeople,EntryTime,EName,Temp,
	ISNULL(Reason,'') AS Reason,
	(SELECT d.DName from Department d WHERE d.Id=Did) AS DName,
	(SELECT c.CompanyName FROM Company c WHERE c.Id=Award_Punish.Cid) AS CName	
	 from Award_Punish 
	where EId=@id AND Temp = 1
	ORDER BY EntryTime desc
/**********************惩罚记录END*************************/

--5:
/************************家庭成员及社会关系****************************/
  select * from Employee_MemberRelation where eid=@id
/************************家庭成员及社会关系END****************************/

--6:
/************************教育经历****************************/
select * from Employee_EducationResume where eid=@id
/************************教育经历END****************************/

--7:
/************************岗位异动********************************/
select
	(select CompanyName from Company where id=ACompanyId) as ACompanyName,
	(select DName from Department where id= (SELECT e.DepartmentId from employee E where Employee_PostMove.EId=E.Id)) as ADepartmentName,
	(select PostName from Post where id= APostId) as PostName,	
	(select Title from Grade G where id= AGradeId) as GradeName,	
	AZhiWu,	
	ISNULL(Execdate,'1900-01-01') AS Execdate,		
	ISNULL(AXZJB,'') AS XZJB
	,applionTime
	from
		Employee_PostMove
	where 
		eid=@id
		and secondAudit=1
order by applionTime
/************************岗位异动END********************************/

--:8
/*************************单位异动*******************************/
select
	(select CompanyName from Company where id=(select Acompanyid from employee E where Id=@id)) as ACompanyName,
	(select DName from Department where id=(select Adepartmentid from employee E where Id=@id)) as ADepartmentName,
	--(select PostName from Post P where id=(select PostId from employee E where Employee_CompanyMove.EId=E.Id)) as
	 (case PostName when '' then (select PostName from Post P where id=(select PostId from employee E where Employee_CompanyMove.EId=E.Id)) else PostName end) as PostName,
	--(select Title from Grade G where id=(select GradeId from employee E where Employee_CompanyMove.EId=E.Id)) as
	 (case GradeName when '' then (select Title from Grade G where id=(select GradeId from employee E where Employee_CompanyMove.EId=E.Id)) else GradeName end) as GradeName,
	AZhiWu,	
	ISNULL(Execdate,'1900-01-01') AS Execdate,
	ISNULL((select E.XZJB FROM employee E where E.id=Employee_CompanyMove.EId),'') AS XZJB
    ,applionTime
	from Employee_CompanyMove
	where eid=@id and  ACompanyId is NOT NULL AND Audit = 1
order by applionTime
/*************************单位异动END*******************************/	

END

